﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using HotelSoft.HotelManagement_DTO;
using System.Collections;
namespace HotelSoft.HotelManagement_DAO
{
    class BaoCaoDoanhThuTheoLoaiDAO
    {

        public ArrayList BaoCaoDoanhThuThang(object AbstractDTO)
        {
            THONGKE_THANGDTO dto = (THONGKE_THANGDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as a"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM + " and month(hd.NgayThanhToan) = " + dto.THANG + "GROUP BY p.TenLP";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuTheoLoaiPhongDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuTheoLoaiPhongDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_LoaiPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_DoanhThu = float.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 30;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

       
        public ArrayList BaoCaoDoanhThuQui(object AbstractDTO)
        {
            THONGKE_QUIDTO dto = (THONGKE_QUIDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as a"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM + " and datepart(q, hd.NgayThanhToan)= " + dto.QUI + "GROUP BY p.TenLP";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuTheoLoaiPhongDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuTheoLoaiPhongDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_LoaiPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_DoanhThu = float.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 3;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        public ArrayList BaoCaoDoanhThuNam(object AbstractDTO)
        {
            THONGKE_NAMDTO dto = (THONGKE_NAMDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as a"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM  + "GROUP BY p.TenLP";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoDoanhThuTheoLoaiPhongDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoDoanhThuTheoLoaiPhongDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_LoaiPhong = ds.Rows[i]["TenLP"].ToString();
                BaoCao.m_DoanhThu = float.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 12;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        //Mat do su dung
        public ArrayList BaoCaoMatDoThang(object AbstractDTO)
        {
            THONGKE_THANGDTO dto = (THONGKE_THANGDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as a"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM + " and month(pdk.NgayDen) = " + dto.THANG
                                + "GROUP BY p.SoPhong";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoMatDoSuDungDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoMatDoSuDungDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_TenPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_SoNgayThue = int.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 30;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        public ArrayList BaoCaoMatDoQui(object AbstractDTO)
        {
            THONGKE_QUIDTO dto = (THONGKE_QUIDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as a"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM + " and datepart(q,pdk.NgayDen) = " + dto.QUI
                                + "GROUP BY p.SoPhong";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoMatDoSuDungDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoMatDoSuDungDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_TenPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_SoNgayThue = int.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 4;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        public ArrayList BaoCaoMatDoNam(object AbstractDTO)
        {
            THONGKE_NAMDTO dto = (THONGKE_NAMDTO)AbstractDTO;
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as a"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM
                                + "GROUP BY p.SoPhong";

            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            BaoCaoMatDoSuDungDTO BaoCao;

            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                BaoCao = new BaoCaoMatDoSuDungDTO();
                BaoCao.m_Stt = i + 1;
                BaoCao.m_TenPhong = ds.Rows[i]["SoPhong"].ToString();
                BaoCao.m_SoNgayThue = int.Parse(ds.Rows[i]["a"].ToString());
                float tl = float.Parse(ds.Rows[i]["a"].ToString());
                float m_tl = tl / 12;
                BaoCao.m_TiLe = m_tl;
                arrDanhSach.Add(BaoCao);
                i++;
            }
            return arrDanhSach;
        }

        /////////////////////////////////////////////////////////

        public DataTable BCDT_THANG(object AbstractDTO)
        {
            THONGKE_THANGDTO dto = (THONGKE_THANGDTO)AbstractDTO;
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as Tong, (Sum(TongTien)/30.0) as Tile"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM + " and month(hd.NgayThanhToan) = " + dto.THANG + "GROUP BY p.TenLP";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCDT_QUI(object AbstractDTO)
        {
            THONGKE_QUIDTO dto = (THONGKE_QUIDTO)AbstractDTO;
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as Tong, (Sum(TongTien)/4.0) as Tile"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM + " and datepart(q, hd.NgayThanhToan)= " + dto.QUI + "GROUP BY p.TenLP";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCDT_NAM(object AbstractDTO)
        {
            THONGKE_NAMDTO dto = (THONGKE_NAMDTO)AbstractDTO;
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as Tong, (Sum(TongTien)/12.0) as Tile"
                                + " FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK"
            + " and year(hd.NgayThanhToan) = " + dto.NAM + "GROUP BY p.TenLP";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCMD_THANG(object AbstractDTO)
        {
            THONGKE_THANGDTO dto = (THONGKE_THANGDTO)AbstractDTO;
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as Tong, (Sum(SoNgayThue)/30.0) as Tile"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM + " and month(pdk.NgayDen) = " + dto.THANG
                                + "GROUP BY p.SoPhong";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCMD_QUI(object AbstractDTO)
        {
            THONGKE_QUIDTO dto = (THONGKE_QUIDTO)AbstractDTO;
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as Tong, (Sum(SoNgayThue)/4.0) as Tile"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM + " and datepart(q,pdk.NgayDen) = " + dto.QUI
                                + "GROUP BY p.SoPhong";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCMD_NAM(object AbstractDTO)
        {
            THONGKE_NAMDTO dto = (THONGKE_NAMDTO)AbstractDTO;
            string searchCommand = "SELECT p.SoPhong,SUM(SoNgayThue) as Tong, (Sum(SoNgayThue)/12.0) as Tile"
                                + " FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + " and year(pdk.NgayDen) = " + dto.NAM
                                + "GROUP BY p.SoPhong";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCDT_NGAY(object AbstractDTO)
        {
            THONGKE_NGAYDTO dto = (THONGKE_NGAYDTO)AbstractDTO;
            string searchCommand = "SELECT p.TenLP,SUM(TongTien) as Tong, ROUND((Sum(TongTien)*100)/(Select Sum(TongTien) From HOADON_THANHTOAN  where NgayThanhToan='" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy") + "'),2) as Tile "
                                + "FROM PHIEUDANGKY pdk,HOADON_THANHTOAN hd, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP and pdk.SoPDK = hd.SoPDK "
                                + "and hd.NgayThanhToan = '" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy") + "' GROUP BY p.TenLP";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCMD_NGAY(object AbstractDTO)
        {
            THONGKE_NGAYDTO dto = (THONGKE_NGAYDTO)AbstractDTO;
            string searchCommand = "SELECT p.SoPhong,count(*) as Tong,((count(*)*100)/(select count(*) from PHIEUDANGKY b where b.NgayDen='" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy") + "')) as Tile "
                                + "FROM PHIEUDANGKY pdk, LOAIPHONG lp, PHONG p "
                                + "WHERE  pdk.SoPhong = p.SoPhong and p.TenLP = lp.TenLP "
                                + "and pdk.NgayDen = '" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy")
                                + "' GROUP BY p.SoPhong";
            return Config.AccessHelper.ExecuteQuery(searchCommand);
        }

        public DataTable BCDT_LOAIPHONG_Tong(object AbstractDTO)
        {
            THONGKE_NGAYDTO dto = (THONGKE_NGAYDTO)AbstractDTO;
            string sql = "SELECT sum(TongTien) From HOADON_THANHTOAN where NgayThanhToan='" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy") + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public DataTable BCMD_PHONG_Tong(object AbstractDTO)
        {
            THONGKE_NGAYDTO dto = (THONGKE_NGAYDTO)AbstractDTO;
            string sql = "SELECT count(*) From PHIEUDANGKY where Ngayden='" + DateTime.Parse(dto.DATE).ToString("MM/dd/yyyy") + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
    }
}
